clear
set more off

// Get efsy data ready
tempfile agg
forval y = 1975/2010 {
	
import delimited using "${raw}/cbp/efsy/`y'/Final Imputed/efsy_cbp_`y'.csv", clear

	// get 4 digit level only (sic years)
	if (`y' <= 1997) {
		// To get total 4 digit counts, 
		// need to detect and remove 3 and 4 digit double counting
		gen code4=naics
		destring code4, replace force
		gen k3=code4/10
		gen f3=floor(k3)
		replace code4=. if k3-f3==0
		drop k3 f3

		gen code3=naics
		destring code3, replace force
		gen k2=code3/100
		gen f2=floor(k2)
		replace code3=. if k2-f2==0
		drop k2 f2
		replace code3=floor(code3/10)

			
		// geo identifier
		gen countyid = fipstate * 1000 + fipscty	
	
		gegen four = sum(lb) if code4!=., by(countyid code3)
		gegen x    = mean(four), by(countyid code3)
		replace lb = lb - x if code4==. & x !=.
		drop four x 
		
		gegen four = sum(ub) if code4!=., by(countyid code3)
		gegen x    = mean(four), by(countyid code3)
		replace ub = ub - x if code4==. & x !=.
		drop four x 
		
		keep if !missing(code3)	

	}
	
	destring naics, force replace
	drop if naics == .

	// geo identifier
cap	gen countyid = fipstate * 1000 + fipscty

	// save for merge
	tempfile temp
	save `temp', replace
	
		
// bring in estab data

use "${wd}/cbp/CPB_`y'_estab.dta", clear

// rename for merge
cap rename siccode2 naics
cap rename sic naics

// for some reason, duplicates in 1975 data (maybe others)
 duplicates drop
 egen g = group(countyid naics)
 egen c = count( countyid) ,by(g) 
 tab c
 cap drop if c>1 & !missing(flag)
 
// merge
	merge 1:1 countyid naics using `temp'
	tab _merge
	
gen miss_flag = 1 if lb>0 & !missing(lb) & testab == 0	

replace testab = 1 if miss_flag == 1

* establishment size brackets
* 1: 1-4 employees
* 2: 5-9
* 3: 10-19
* 4: 20-49
* 5: 50-99
* 6: 100-249
* 7: 250-499
* 8: 500-999
* 9: 1000+
* 10: 1000-1499
* 11: 1500-2499
* 12: 2500-4999
* 13: 5000+

local i = 1
foreach letter in A B C D E F G H I J K L M { 
	replace ctyemp`i' = 1 if flag == "`letter'" & miss_flag == 1
	local ++i
	}
	
	capture append using `agg'
	save `agg', replace
}

drop g c adjusted miss_flag
rename _merge estab_merge

save ${wd}/CBP/CBP_Panel.dta, replace

